﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using AmuTools;
using app.Api;
using System.Data.SqlClient;
using System.Data;

namespace app.Models
{
    [Model(TableName = "t_statistic", PrimaryKey = "id", IdentityInsert = true)]
    public class Statistic
    {
        [Field(Description = "唯一标识符")]
        public int id { get; set; }


        [Field(Description = "地区")]
        public string area { get; set; }


        [Field(Description = "年")]
        public int year { get; set; }


        [Field(Description = "月")]
        public int month { get; set; }


        [Field(Description = "日")]
        public int day { get; set; }

        [Field(DataType = "char(13)", Description = "当天的时间戳")]
        public string date { get; set; }


        [Field(Description = "注册会员数量")]
        public int member_count { get; set; }


        [Field(Description = "发布需求数量")]
        public int demand_count { get; set; }


        [Field(Description = "发布服务数量")]
        public int service_count { get; set; }


        [Field(Description = "服务对接数量")]
        public int abutment_count { get; set; }


        // 将一天的统计数据保存到数据库
        public static void SaveOneDayData(DateTime date)
        {
            long temp = long.Parse(DateTimeEx.DateTimeToTimeStamp(date));
            temp = temp/86400000*86400000;
            string begin_time = temp.ToString();
            string end_time = (temp + 86400000).ToString();

            // 首先获取每个表统计数据
            string sql_str = string.Format(@"
                -- 会员
                select area, count(*) member_count
                from t_member 
                where create_time >= @begin_time and create_time < @end_time
                group by area
                -- 发布的需求
                select d.demand_area area, count(*) demand_count
                from t_member m, t_demand_item d
                where d.member_id = m.id and d.create_time >= @begin_time and d.create_time < @end_time
                group by d.demand_area
                -- 发布的服务
                select m.area, count(*) service_count
                from t_member m, t_service_item s
                where s.member_id = m.id and s.create_time >= @begin_time and s.create_time < @end_time
                group by m.area
                -- 对接
                select m.area, count(*) abutment_count
                from t_abutment a, t_member m
                where a.service_member_id = m.id and a.create_time >= @begin_time and a.create_time < @end_time
                group by m.area");
            DataSet ds = G.DB.Get(sql_str, new SqlParameter[] {
                G.DB.CreateSqlParameter("@begin_time", begin_time),
                G.DB.CreateSqlParameter("@end_time", end_time)
            }).DataSet;

            // 将多个表的数据按日期和地区合并
            int year = date.Year;
            int month = date.Month;
            int day = date.Day;
            Dictionary<string, Statistic> dic = new Dictionary<string, Statistic>();
            foreach(DataRow dr in ds.Tables[0].Rows)
            {
                string area = dr["area"].ToString();
                if (!dic.ContainsKey(area))
                {
                    dic[area] = new Statistic { area = area, year = year, month = month, day = day, date = begin_time };
                }
                dic[area].member_count = (int)dr["member_count"];
            }
            foreach (DataRow dr in ds.Tables[1].Rows)
            {
                string area = dr["area"].ToString();
                if (!dic.ContainsKey(area))
                {
                    dic[area] = new Statistic { area = area, year = year, month = month, day = day, date = begin_time };
                }
                dic[area].demand_count = (int)dr["demand_count"];
            }
            foreach (DataRow dr in ds.Tables[2].Rows)
            {
                string area = dr["area"].ToString();
                if (!dic.ContainsKey(area))
                {
                    dic[area] = new Statistic { area = area, year = year, month = month, day = day, date = begin_time };
                }
                dic[area].service_count = (int)dr["service_count"];
            }
            foreach (DataRow dr in ds.Tables[3].Rows)
            {
                string area = dr["area"].ToString();
                if (!dic.ContainsKey(area))
                {
                    dic[area] = new Statistic { area = area, year = year, month = month, day = day, date = begin_time };
                }
                dic[area].abutment_count = (int)dr["abutment_count"];
            }

            // 删掉原来对当天的统计数据，再把新的数据保存起来
            G.DB.DeleteByCondition<Statistic>(string.Format("year = {0} and month = {1} and day = {2}", year, month, day));
            G.DB.Insert<Statistic>(dic.Values.ToList<Statistic>());
        }
    }
}